import os
import glob
import shutil
import pandas as pd
import openpyxl

problem_files_folder = r'problems'
os.makedirs(problem_files_folder, exist_ok=True)


def dedupe_by_pandas(excel_file, unique_file):
    """
    pandas内置去重方法
    https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.drop_duplicates.html
    :param excel_file:
    :param unique_file:
    :return:
    """
    df = pd.read_excel(excel_file, sheet_name=0, header=None)
    df = df.drop_duplicates()
    df.to_excel(unique_file, index=False, header=None)


def dedupe_by_openpyxl(excel_file, unique_file):
    """
    手写去重，目前没保留原有顺序
    当然可以稍改动进而保留顺序
    :param excel_file:
    :param unique_file:
    :return:
    """
    wbr = openpyxl.load_workbook(excel_file)
    wsr = wbr.active

    wb = openpyxl.Workbook()
    sheet = wb.active
    sheet.title = "uniques"
    row = 0
    uniques = set()

    for cell in wsr.iter_rows(min_row=1,
                              max_row=wsr.max_row,
                              min_col=1,
                              max_col=1):

        if cell[0].value is not None:
            s = cell[0].value
            if s not in uniques:
                row += 1
                uniques.add(s)
                sheet.cell(row=row, column=1).value = s
    wb.save(unique_file)


def remove_duplicates(excel_file, unique_file):
    try:
        with open(excel_file, "r") as file:
            file.close()
    except IOError:
        print(f"{excel_file} 正被占用无法操作，跳过")
        return

    try:
        # 任意选一种去重方法
        # dedupe_by_pandas(excel_file, unique_file)
        dedupe_by_openpyxl(excel_file, unique_file)
        print(excel_file, '-->', unique_file)
    except Exception as e:
        # 备份问题文件
        shutil.copy2(excel_file, problem_files_folder)
        print(excel_file, e)


def extract_bookmarks(excel_folder):
    unique_xlsx = '_unique.xlsx'
    for excel_file in glob.glob(rf'{excel_folder}\**\*.xlsx', recursive=True):
        if excel_file.endswith(unique_xlsx):
            continue
        unique_file = excel_file.replace('.xlsx', unique_xlsx)
        remove_duplicates(excel_file, unique_file)


if __name__ == '__main__':
    excel_folder = r'D:\dl\20221106测试文件\测试文件'
    extract_bookmarks(excel_folder)
